Contents

Mapping the US Logistics Network

Mapping the US Logistics Network 

import pandas as pd
import matplotlib.pyplot as plt
import geopandas as gpd
import folium
import contextily as cx
import rtree

/Users/jnapolitano/venvs/finance/lib/python3.9/site-packages/geopandas/_compat.py:111: UserWarning: The Shapely GEOS version (3.10.2-CAPI-1.16.0) is incompatible with the GEOS version PyGEOS was compiled with (3.10.1-CAPI-1.16.0). Conversions between both will be slow.
  warnings.warn(

Categorizing Rail Facilities by Nearest Ports

Intermodal Rail Data 

## Importing our DataFrames

gisfilepath = "/Users/jnapolitano/Projects/rail-mapping/intermodal/Intermodal_Freight_Facilities_RailTOFCCOFC.geojson"

rail_to_all_df = gpd.read_file(gisfilepath)

rail_to_all_df = rail_to_all_df.to_crs(epsg=3857)

rail_to_all_df
OBJECTID TERMINAL TERM_ADDRE CITY STATE ZIP_CODE PORT DATE RAIL_COMPA EQUIPMENT ACCESS_ROA SPLC LAT LON geometry
0 1 Albany, NY - Mechanicville 50 Route 67 Mechanicville NY 12118 2018-08-01T00:00:00+00:00 NS COFC 171760 42.914715 -73.709533 POINT (-8205307.680 5298999.597)
1 2 Albuquerque, NM 100 Woodward Road S.E. Albuquerque NM 87102 2018-09-01T00:00:00+00:00 BNSF TOFC/COFC 774900 35.047580 -106.653344 POINT (-11872595.946 4170348.957)
2 3 Alliance, TX 1111 Intermodal Parkway Haslet TX 76052 2018-09-02T00:00:00+00:00 BNSF TOFC/COFC 668100 32.999881 -97.347026 POINT (-10836621.365 3895288.168)
3 4 Arcadia, WI One Ashley Way Arcadia WI 54612 2018-09-01T00:00:00+00:00 CN TOFC/COFC South Washington St. 328754 44.251243 -91.509663 POINT (-10186809.088 5504405.295)
4 5 Atlanta, GA - Austell 6000 Dr. Luke Glenn Garrett, Jr. Memorial Highway Austell GA 30106 2018-08-01T00:00:00+00:00 NS TOFC/COFC 456496 33.825798 -84.655069 POINT (-9423759.174 4005434.870)
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
176 177 Wilmington, CA - TraPac Intermodal Container T... 630 West Harry Bridges Boulevard Wilmington CA 90744 Port of Los Angeles 2018-12-07T00:00:00+00:00 PHL TOFC/COFC 883257 33.770320 -118.273289 POINT (-13166122.306 3998003.151)
177 178 Wilmington, NC - Container Terminal 1 Shipyard Boulevard Wilmington NC 28401 Port of Wilmington 2018-09-11T00:00:00+00:00 CSX COFC 409160 34.191224 -77.952419 POINT (-8677623.589 4054507.730)
178 179 Winter Haven, FL - Central Florida Intermodal ... 3935 Intermodal Drive Winter Haven FL 33884 2018-09-11T00:00:00+00:00 CSX, EVWR COFC 497540 27.939953 -81.693226 POINT (-9094048.320 3241405.344)
179 180 Worcester, MA 165 Grafton Street Worcester MA 01604 2018-09-11T00:00:00+00:00 CSX TOFC/COFC 145460 42.259951 -71.789244 POINT (-7991542.086 5199998.731)
180 181 Wylie, TX 2800 State Highway 78 North Wylie TX 75098 2018-08-03T00:00:00+00:00 KCS TOFC/COFC 663676 33.025555 -96.509452 POINT (-10743383.053 3898696.454)

181 rows × 15 columns

Major Ports Data 

gisfilepath = "/Users/jnapolitano/Projects/rail-mapping/shipping/Major_Ports.geojson"
major_ports_df = gpd.read_file(gisfilepath)
major_ports_df = major_ports_df.to_crs(epsg=3857)

major_ports_df
OBJECTID_1 OBJECTID ID PORT PORT_NAME GRAND_TOTA FOREIGN_TO IMPORTS EXPORTS DOMESTIC geometry
0 1 1 124 C4947 Unalaska Island, AK 1652281 1236829 426251 810578 415452 POINT (-18806114.399 7124760.122)
1 2 2 85 C4410 Kahului, Maui, HI 3615449 20391 20391 0 3595058 POINT (-17418472.419 2379793.012)
2 3 3 116 C4405 Kawaihae Harbor, HI 2034621 0 0 0 2034621 POINT (-17347283.605 2277507.024)
3 4 4 113 C4430 Nawiliwili, Kauai, HI 2069212 22066 22066 0 2047146 POINT (-17739105.948 2506856.666)
4 5 5 52 C4458 Barbers Point, Oahu, HI 9854176 7384757 6683744 701013 2469419 POINT (-17600602.238 2427355.584)
... ... ... ... ... ... ... ... ... ... ... ...
145 146 146 145 C4240 Sacramento, CA 898044 898044 733008 165036 0 POINT (-13625399.029 4552149.948)
146 147 147 142 I2094 Morgan City, LA, Port of 991600 51 51 0 991549 POINT (-10161297.777 3429249.067)
147 148 148 144 I6111 Little Rock, AR, Port of 914190 0 0 0 914190 POINT (-10281276.811 4135486.697)
148 149 149 150 I6112 Fort Smith, AR, Port of 788939 0 0 0 788939 POINT (-10506939.342 4224548.117)
149 150 150 149 L3230 Buffalo, NY 791358 244512 244512 0 546846 POINT (-8784162.687 5302483.766)

150 rows × 11 columns

Intermodal Rail Stations Joined by Nearest Major Port 

major_transit_nodes = rail_to_all_df.sjoin_nearest(major_ports_df)
major_transit_nodes.drop(columns=['OBJECTID_right', 'index_right','OBJECTID_1','OBJECTID_left','PORT_left'], inplace=True)
print(major_transit_nodes.columns)
major_transit_nodes

Index(['TERMINAL', 'TERM_ADDRE', 'CITY', 'STATE', 'ZIP_CODE', 'DATE',
       'RAIL_COMPA', 'EQUIPMENT', 'ACCESS_ROA', 'SPLC', 'LAT', 'LON',
       'geometry', 'ID', 'PORT_right', 'PORT_NAME', 'GRAND_TOTA', 'FOREIGN_TO',
       'IMPORTS', 'EXPORTS', 'DOMESTIC'],
      dtype='object')
TERMINAL TERM_ADDRE CITY STATE ZIP_CODE DATE RAIL_COMPA EQUIPMENT ACCESS_ROA SPLC ... LON geometry ID PORT_right PORT_NAME GRAND_TOTA FOREIGN_TO IMPORTS EXPORTS DOMESTIC
0 Albany, NY - Mechanicville 50 Route 67 Mechanicville NY 12118 2018-08-01T00:00:00+00:00 NS COFC 171760 ... -73.709533 POINT (-8205307.680 5298999.597) 73 C505 Albany, NY 5712209 942081 681283 260798 4770128
165 Syracuse, NY 600 Fremont Road Syracuse NY 13057 2018-09-11T00:00:00+00:00 CSX TOFC/COFC Old Fairmont Rd. 182340 ... -76.036054 POINT (-8464294.813 5322817.024) 73 C505 Albany, NY 5712209 942081 681283 260798 4770128
1 Albuquerque, NM 100 Woodward Road S.E. Albuquerque NM 87102 2018-09-01T00:00:00+00:00 BNSF TOFC/COFC 774900 ... -106.653344 POINT (-11872595.946 4170348.957) 128 C4100 San Diego, CA 1572162 1301084 1244610 56474 271078
136 Phoenix, AZ - Glendale 5281 North Tom Murray Avenue Glendale AZ 85301 2018-09-17T00:00:00+00:00 BNSF COFC 797000 ... -112.160564 POINT (-12485656.872 3963938.119) 128 C4100 San Diego, CA 1572162 1301084 1244610 56474 271078
175 Tucson, AZ 6800 E. Century Park Drive Tucson AZ 85706 2018-09-02T00:00:00+00:00 UP COFC Third Driveway Entrance 799000 ... -110.848336 POINT (-12339580.319 3779918.915) 128 C4100 San Diego, CA 1572162 1301084 1244610 56474 271078
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
166 Tacoma, WA - North Yard 711 Port of Tacoma Road Tacoma WA 98421 2018-09-26T00:00:00+00:00 BNSF COFC 846203 ... -122.407768 POINT (-13626370.403 5986180.882) 32 C4720 Tacoma, WA 22849184 19624407 6814144 12810263 3224777
167 Tacoma, WA - South Yard 1101 Milwaukee Way Tacoma WA 98421 2018-09-27T00:00:00+00:00 BNSF COFC 846212 ... -122.412016 POINT (-13626843.288 5985216.785) 32 C4720 Tacoma, WA 22849184 19624407 6814144 12810263 3224777
168 Tacoma, WA - Tacoma South (TacSim) 1738 Milwaukee Way Tacoma WA 98421 2018-09-01T00:00:00+00:00 UP COFC 846214 ... -122.407759 POINT (-13626369.401 5984209.166) 32 C4720 Tacoma, WA 22849184 19624407 6814144 12810263 3224777
169 Tampa, FL 1901 North 62nd Street Tampa FL 33619 2018-09-11T00:00:00+00:00 CSX COFC 497800 ... -82.383968 POINT (-9170941.367 3244033.473) 22 C2021 Tampa, FL 31006487 12216828 8053152 4163676 18789659
178 Winter Haven, FL - Central Florida Intermodal ... 3935 Intermodal Drive Winter Haven FL 33884 2018-09-11T00:00:00+00:00 CSX, EVWR COFC 497540 ... -81.693226 POINT (-9094048.320 3241405.344) 22 C2021 Tampa, FL 31006487 12216828 8053152 4163676 18789659

181 rows × 21 columns

Mapping Intermodal Rail Facilities Categorized by Major Ports 

major_transit_nodes.explore()
Make this Notebook Trusted to load map: File -> Trust Notebook

Air Freight to Truck Facilites Categorized by Major Port 

gisfilepath = "/Users/jnapolitano/Projects/rail-mapping/intermodal/Intermodal_Freight_Facilities_Air-to-Truck.geojson"
air_freight_to_truck_df = gpd.read_file(gisfilepath)
air_freight_to_truck_df = air_freight_to_truck_df.to_crs(epsg=3857)

air_freight_to_truck_df
OBJECTID LOCID FACILITY_C DATE EST_AREA geometry
0 1 PIT Delta Air Lines; United Parcel Service 09/2018 74000 POINT (-8932265.943 4939618.132)
1 2 PIT Federal Express 09/2018 73000 POINT (-8931996.093 4939519.257)
2 3 PIT 09/2018 65000 POINT (-8931632.274 4939487.129)
3 4 PIT 09/2018 55000 POINT (-8930019.150 4938284.560)
4 5 RDU Federal Express 09/2018 63000 POINT (-8769327.059 4285470.308)
... ... ... ... ... ... ...
399 400 IAH British Airways; Swissport Cargo Services 12/2018 88000 POINT (-10609992.124 3503138.495)
400 401 IAH Air General; American Airlines; Dachser 12/2018 70000 POINT (-10609855.207 3503137.177)
401 402 IAH Cargo Airport Services Texas; Cargolux Airline... 12/2018 149000 POINT (-10609937.414 3502943.849)
402 403 IAH Singapore Airlines 12/2018 149000 POINT (-10609922.972 3502538.518)
403 404 IAH United Air Lines 12/2018 110000 POINT (-10613775.002 3500908.358)

404 rows × 6 columns

Air Freight Hubs joined by Major Ports 

major_air_freight = air_freight_to_truck_df.sjoin_nearest(major_ports_df)

major_air_freight.drop(columns=['OBJECTID_right', 'index_right','OBJECTID_1','OBJECTID_left'], inplace=True)
print(major_air_freight.columns)

major_air_freight

Index(['LOCID', 'FACILITY_C', 'DATE', 'EST_AREA', 'geometry', 'ID', 'PORT',
       'PORT_NAME', 'GRAND_TOTA', 'FOREIGN_TO', 'IMPORTS', 'EXPORTS',
       'DOMESTIC'],
      dtype='object')
LOCID FACILITY_C DATE EST_AREA geometry ID PORT PORT_NAME GRAND_TOTA FOREIGN_TO IMPORTS EXPORTS DOMESTIC
0 PIT Delta Air Lines; United Parcel Service 09/2018 74000 POINT (-8932265.943 4939618.132) 33 I2358 Pittsburgh, PA 21567015 0 0 0 21567015
1 PIT Federal Express 09/2018 73000 POINT (-8931996.093 4939519.257) 33 I2358 Pittsburgh, PA 21567015 0 0 0 21567015
2 PIT 09/2018 65000 POINT (-8931632.274 4939487.129) 33 I2358 Pittsburgh, PA 21567015 0 0 0 21567015
3 PIT 09/2018 55000 POINT (-8930019.150 4938284.560) 33 I2358 Pittsburgh, PA 21567015 0 0 0 21567015
4 RDU Federal Express 09/2018 63000 POINT (-8769327.059 4285470.308) 70 C766 Wilmington, NC 6039927 5660231 3377654 2282577 379696
... ... ... ... ... ... ... ... ... ... ... ... ... ...
388 HNL Alaska Airlines 12/2018 42000 POINT (-17578969.072 2429783.328) 41 C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
389 HNL United Parcel Service 12/2018 30000 POINT (-17578874.086 2429878.269) 41 C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
390 HNL American Airlines; Delta Air Lines 12/2018 20000 POINT (-17578581.567 2431567.128) 41 C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
391 HSV 12/2018 130000 POINT (-9658266.854 4115396.770) 119 I2371 Guntersville, AL 1918572 0 0 0 1918572
392 HSV Panalpina 12/2018 189000 POINT (-9658200.443 4114983.062) 119 I2371 Guntersville, AL 1918572 0 0 0 1918572

409 rows × 13 columns

Mapping Air Freight Hubs Categorized by Nearest Major Port Port 

major_air_freight.explore()
Make this Notebook Trusted to load map: File -> Trust Notebook

Overlaying Shipping Networks

Shipping Data 

gisfilepath = "//Users/jnapolitano/Projects/rail-mapping/shipping/Navigable_Waterway_Lines.geojson"
shipping_network = gpd.read_file(gisfilepath)
shipping_network = shipping_network.to_crs(epsg=3857)

shipping_network
OBJECTID ID LENGTH DIR LINKNUM ANODE BNODE LINKNAME RIVERNAME AMILE ... NUM_PAIRS WHO_MOD DATE_MOD HEADING STATE FIPS FIPS2 NON_US Shape_Length geometry
0 1 13814 2237.99 0 887200 58220 56950 HONOLULU, OAHU, HI-WAKE I, UM SPINE PACIFIC DEEP WATER SPINE 0.0 ... 2 V A 01201997 W XX 99 00 PC 3.623416e+07 LINESTRING (18657013.074 2207752.325, -1757651...
1 2 13820 1312.41 0 820800 51880 51990 MITROFANIA BAY, AK METROFANIA BAY 0.0 ... 6 V 02051997 E AK 02 00 4.193663e+07 LINESTRING (-17677356.025 7542570.542, -176582...
2 3 13857 36.37 0 886900 58330 58320 COLUMBIA RIVER ENTRANCE, OR & WA SPARKMAN-YBOR CHANNELS 0.0 ... 2 V A 09141998 E XX 99 00 XX 6.541705e+04 LINESTRING (14304554.567 3061141.039, 14354158...
3 4 13863 41.69 0 888500 58240 58230 APRA HARBOR, GUAM ISLAND, GU APRA HARBOR 0.0 ... 2 V A 01201997 E GU 66 00 6.903228e+04 LINESTRING (16100505.308 1511190.911, 16167174...
4 5 13870 121.75 0 889400 58520 58230 SAIPAN MARIANA ISLAND 0.0 ... 2 W 04202017 S MP 69 00 2.034368e+05 LINESTRING (16223020.645 1715270.170, 16220315...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
6868 6869 372649 4.44 0 207800 8300 38110 ATL DEEP WATER SPINE ACCESS ATLANTIC DEEP WATER ACCESS 0.0 ... 2 V A 01201997 S XX 99 00 AT 1.001580e+04 LINESTRING (-7529113.797 5524979.655, -7526001...
6869 6870 372655 10.23 0 352900 8310 8300 JONESPORT, ME JONESPORT HARBOR 0.0 ... 3 R S ME 23 00 2.310771e+04 LINESTRING (-7526965.331 5547218.511, -7530662...
6870 6871 372667 6.58 0 224100 36970 36960 BEALS HARBOR,ME BEALS HARBOR 4.0 ... 3 R S ME 23 00 1.485327e+04 LINESTRING (-7523701.444 5542231.099, -7521614...
6871 6872 372678 7.41 0 223800 37000 36990 BEAL & GREAT WASS ISLANDS, ME EASTERN BAY 0.0 ... 2 R S ME 23 00 1.673232e+04 LINESTRING (-7522918.868 5545348.106, -7516423...
6872 6873 372684 4.82 0 223500 37030 37020 HEAD HARBOR, ME HEAD HARBOR 0.0 ... 2 R S ME 23 00 1.087250e+04 LINESTRING (-7516772.919 5542045.392, -7513218...

6873 rows × 31 columns

Calculating Major Port for Node in Shipping Network 

port_shipping = shipping_network.sjoin_nearest(major_ports_df)
port_shipping.drop(columns = ['OBJECTID_left','DIR', 'ANODE', 'BNODE', 'ID_left', 'AMILE', 'BMILE', 'LENGTH1', 'LENGTH_SRC', 'SHAPE_SRC', 'GEO_CLASS', 'FUNC_CLASS','OBJECTID_1','CHART_ID', 'index_right', 'NUM_PAIRS', 'CHART_ID', 'DATE_MOD', 'WHO_MOD', 'OBJECTID_right','ID_right'],inplace=True)
print(port_shipping.columns)
port_shipping

Index(['LENGTH', 'LINKNUM', 'LINKNAME', 'RIVERNAME', 'LINKTYPE', 'CTRL_DEPTH',
       'WATERWAY', 'WTWY_TYPE', 'HEADING', 'STATE', 'FIPS', 'FIPS2', 'NON_US',
       'Shape_Length', 'geometry', 'PORT', 'PORT_NAME', 'GRAND_TOTA',
       'FOREIGN_TO', 'IMPORTS', 'EXPORTS', 'DOMESTIC'],
      dtype='object')
LENGTH LINKNUM LINKNAME RIVERNAME LINKTYPE CTRL_DEPTH WATERWAY WTWY_TYPE HEADING STATE ... NON_US Shape_Length geometry PORT PORT_NAME GRAND_TOTA FOREIGN_TO IMPORTS EXPORTS DOMESTIC
0 2237.99 887200 HONOLULU, OAHU, HI-WAKE I, UM SPINE PACIFIC DEEP WATER SPINE CORPS 99 9246 5 W XX ... PC 3.623416e+07 LINESTRING (18657013.074 2207752.325, -1757651... C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
39 2616.09 887600 PAGO PAGO HARBOR, AMERICAN SAMOA I PACIFIC DEEP WATER SPINE CORPS 40 4486 5 E XX ... PC 4.312473e+06 LINESTRING (-17576512.700 2422111.245, -190123... C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
40 2040.92 887700 CANTON ISLAND PHOENIX GROUP PACIFIC DEEP WATER SPINE CORPS 35 4438 5 S XX ... PC 3.366424e+06 LINESTRING (-17576512.700 2422111.245, -193695... C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
41 722.38 887300 JOHNSTON ISLAND, HI PACIFIC DEEP WATER SPINE CORPS 35 4436 5 E XX ... PC 1.231778e+06 LINESTRING (-18701674.453 1920825.040, -175765... C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
122 22.69 896100 KALAUPAPA HARBOR, MOLOKAI, HI KALAUPAPA HARBOR CORPS 12 4460 1 W HI ... 3.918469e+04 LINESTRING (-17475957.804 2414714.487, -174860... C4420 Honolulu, HI 15181890 1439915 1146253 293662 13741975
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5914 0.99 436100 SCHUYLKILL CANAL, PA SCHUYLKILL CANAL CORPS 12 254 9 W PA ... 2.078462e+03 LINESTRING (-8368689.850 4850736.303, -8370165... C552 Philadelphia, PA 26656373 16188350 15136804 1051546 10468023
5915 1.02 4440000 SCHUYLKILL RIVER, PA SCHUYLKILL RIVER CWIS 26 557 6 S PA ... 2.152305e+03 LINESTRING (-8372532.376 4855477.977, -8372632... C552 Philadelphia, PA 26656373 16188350 15136804 1051546 10468023
5916 1.57 4450000 SCHUYLKILL RIVER, PA SCHUYLKILL RIVER CWIS 22 557 6 S PA ... 3.292771e+03 LINESTRING (-8370893.976 4857560.431, -8371269... C552 Philadelphia, PA 26656373 16188350 15136804 1051546 10468023
5917 3.31 436300 DELAWARE R, PHILA, PA (TO THE SEA) DELAWARE RIVER CORPS 40 5250 6 W NJ ... 6.945059e+03 LINESTRING (-8364429.653 4849662.658, -8364809... C552 Philadelphia, PA 26656373 16188350 15136804 1051546 10468023
5918 5.18 436400 BIG TIMBER CREEK, NJ BIG TIMBER CREEK CORPS 10 259 6 N NJ ... 1.086916e+04 LINESTRING (-8358718.740 4843349.213, -8358942... C552 Philadelphia, PA 26656373 16188350 15136804 1051546 10468023

6902 rows × 22 columns

port_shipping.explore()
Make this Notebook Trusted to load map: File -> Trust Notebook

previous

Analzying Intermodal Connections in the United States Supply Chain.

next

Contact Justin Napolitano